using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

using Pegasus.Diagnostics;

namespace Pegasus.Data.SqlClient
{
	/// <summary>
	/// This is a helper class is used to access a SQL database;
	/// </summary>
	/// <remarks>
	/// The class opens the connections to the database and allows the user to 
	/// execute a SQL statement or Stored Procedure and get different types
	/// of return objects (DataReaders, DataSet, affected rows, etc...)
	/// <code>
	/// public class MyApp
	/// {
	///		private SqlDatabaseConnection m_conn;
	///		
	///		public MyApp()
	///		{
	///			m_conn = new SqlDatabaseConnection( "sqlserver", "thedatabase", "myacount", "mypassword" );
	///		}
	///		
	///		public void DoSomeDatabaseStuff( int aNumber )
	///		{
	///			SqlDatabaseAccess dba = m_conn.CreateDatabaseAccess()
	///			int affectedRows = dba.SqlNonQuery( "INSERT INTO MyTable ( number ) VALUES( '{0}' )", aNumber );
	///			dba.Close();
	///		}
	///	}
	/// </code>
	/// </remarks>
	public class SqlDatabaseAccess : IDisposable
	{
		// Local Instance Values
		private SqlDatabaseConnection m_databaseConnection;
		private SqlConnection m_sqlConnection;
		private SqlTransaction m_sqlTransaction;
		private int m_timeoutRetries = 1;
		private int m_queryTimeout = 30;

		// Private constents
		private const string RETURN_PARAMETER = "@RETURN_VALUE";


		/// <summary>
		/// The type of execute and return value to execute.
		/// </summary>
		private enum SqlExecuteType 
		{
			NonQuery,
			NonQueryWithReturn,
			Reader,
			Scalar,
			DataTable,
			DataSet,
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="SqlDatabaseAccess"/> class.
		/// </summary>
		/// <param name="databaseConnection">The database connection.</param>
		/// <param name="transacted">if set to <c>true</c> [transacted].</param>
		internal SqlDatabaseAccess( SqlDatabaseConnection databaseConnection, bool transacted )
		{
			// Check params
			ParamCode.AssertNotNull( databaseConnection, "databaseConnection" );

			m_databaseConnection = databaseConnection;
            m_queryTimeout = databaseConnection.ConnectionTimeout;

			m_sqlConnection = new SqlConnection( m_databaseConnection.ConnectionString );
			m_sqlConnection.Open();

			if( transacted )
			{
				m_sqlTransaction = m_sqlConnection.BeginTransaction();
			}
		}

		/// <summary>
		/// The call to the database are wrapped in a transaction.
		/// </summary>
		public bool IsTransacted
		{
			get
			{
				return ( m_sqlTransaction != null );
			}
		}

		/// <summary>
		/// Get/Set the number of time to retry the query/stored proce if the server 
		/// returns a timeout error. Values can be 0 - 5, Default is 1.
		/// </summary>
		public int TimeoutRetries
		{
			get
			{
				return m_timeoutRetries;
			}

			set
			{
				ParamCode.Assert( value > -1 && value < 6, "TimeoutRetries" );
				m_timeoutRetries = value;
			}
		}

		/// <summary>
		/// Gets or sets the query timeout. The time in seconds to wait for the command to execute. The default is 30 seconds.
		/// </summary>
		/// <value>The query timeout.</value>
		public int QueryTimeout
		{
			get
			{
				return m_queryTimeout;
			}
		
			set
			{
				m_queryTimeout = value;
			}
		}

		/// <summary>
		/// Dispose of the resouce that the object is using.
		/// </summary>
		public void Dispose()
		{
			lock( this )
			{
				// release the transaction object
				if( m_sqlTransaction != null )
				{
					// If the transaction is still valid then commit the object.
					if( m_sqlTransaction.Connection != null )
					{
						Commit();
					}

					m_sqlTransaction = null;
				}

				// Close the connection
				if( m_sqlConnection != null )
				{
					m_sqlConnection.Close();
					m_sqlConnection = null;
				}
			}
		}

		/// <summary>
		/// Close the connection to the database can clean up all resouces.  If the 
		/// database is being transacted the transaction is commited.
		/// </summary>
		public void Close()
		{
			Dispose();
		}

		/// <summary>
		/// Commit the database transaction.
		/// </summary>
		/// <remarks>
		/// If the object is not transacted, then this call does nothing.
		/// </remarks>
		public void Commit()
		{
			lock( this )
			{
				if( m_sqlTransaction != null )
				{
					m_sqlTransaction.Commit();
				}
			}
		}

		/// <summary>
		/// Rollback the database transaction.
		/// </summary>
		/// <remarks>
		/// If the object is not transacted, then this call does nothing.
		/// </remarks>
		public void Rollback()
		{
			lock( this )
			{
				if( m_sqlTransaction != null )
				{
					m_sqlTransaction.Rollback();
				}
			}
		}

		/// <summary>
		/// Rollback the database transaction to the given save point.
		/// </summary>
		/// <param name="savePoint">The name of the save point to rollback to.</param>
		/// <remarks>
		/// If the object is not transacted, then this call does nothing.
		/// </remarks>
		public void Rollback( string savePoint )
		{
			// Check Params
			ParamCode.AssertNotEmpty( savePoint, "savePoint" );

			lock( this )
			{
				if( m_sqlTransaction != null )
				{
					m_sqlTransaction.Rollback( savePoint );
				}
			}
		}

		/// <summary>
		/// Creates a savepoint in the transaction that can be used to roll back 
		/// a portion of the transaction, and specifies the savepoint name.
		/// </summary>
		/// <param name="savePoint">The name of the save point.</param>
		/// <remarks>
		/// If the object is not transacted, then this call does nothing.
		/// </remarks>
		public void Save( string savePoint )
		{
			// Check Params
			ParamCode.AssertNotEmpty( savePoint, "savePoint" );

			lock( this )
			{
				if( m_sqlTransaction != null )
				{
					m_sqlTransaction.Save( savePoint );
				}
			}
		}

		/// <summary>
		/// Execute a non-query sql statement using a format string and a set of arguments.
		/// </summary>
		/// <param name="sqlFormat">The sql format string.</param>
		/// <param name="args">The arguments for the string.</param>
		/// <returns>The number of rows affected.</returns>
		public int SqlNonQuery( string sqlFormat, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sqlFormat, "sqlFormat" );
			// args can be null or array of objects

			return SqlNonQuery( string.Format( sqlFormat, args ) );
		}

		/// <summary>
		/// Execute a non-query sql statement.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>The number of rows affected.</returns>
		public int SqlNonQuery( string sql )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sql, "sql" );

			return (int) ExecuteCommand( SqlExecuteType.NonQuery, BuildSqlCommand( sql ) );
		}
		
		/// <summary>
		/// Execute a non-query stored procedure.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>The number of rows affected.</returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public int StoredProcNonQuery( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return (int) ExecuteStoredProc( SqlExecuteType.NonQuery, proc, args );
		}

		/// <summary>
		/// Execute a non-query stored procedure that has a return value.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>The return value from the stored procedure.</returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public object StoredProcWithReturn( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return ExecuteStoredProc( SqlExecuteType.NonQueryWithReturn, proc, args );
		}

		/// <summary>
		/// Execute a sql statement using a format string and a set of arguments.
		/// </summary>
		/// <param name="sqlFormat">The sql format string.</param>
		/// <param name="args">The arguments for the string.</param>
		/// <returns>A SqlDataReader bound to the data.</returns>
		public SqlDataReader SqlReader( string sqlFormat, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sqlFormat, "sqlFormat" );
			// args can be null or array of objects

			return SqlReader( string.Format( sqlFormat, args ) );
		}

		/// <summary>
		/// Execute a reader sql statement.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>A SqlDataReader bound to the data.</returns>
		public SqlDataReader SqlReader( string sql )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sql, "sql" );

			return (SqlDataReader) ExecuteCommand( SqlExecuteType.Reader, BuildSqlCommand( sql ) );
		}
		
		/// <summary>
		/// Execute a reader stored procedure.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>A SqlDataReader bound to the data.</returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public SqlDataReader StoredProcReader( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return (SqlDataReader) ExecuteStoredProc( SqlExecuteType.Reader, proc, args );
		}

		/// <summary>
		/// Execute a sql statement using a format string and a set of arguments.
		/// </summary>
		/// <param name="sqlFormat">The sql format string.</param>
		/// <param name="args">The arguments for the string.</param>
		/// <returns>A DataSet object.</returns>
		public DataTable SqlDataTable( string sqlFormat, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sqlFormat, "sqlFormat" );
			// args can be null or array of objects

			return SqlDataTable( string.Format( sqlFormat, args ) );
		}

		/// <summary>
		/// Execute a reader sql statement.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>A DataSet object.</returns>
		public DataTable SqlDataTable( string sql )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sql, "sql" );

			return (DataTable) ExecuteCommand( SqlExecuteType.DataTable, BuildSqlCommand( sql ) );
		}

		/// <summary>
		/// Execute a data set stored procedure.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>A DataSet object.</returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public DataTable StoredProcDataTable( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return (DataTable) ExecuteStoredProc( SqlExecuteType.DataTable, proc, args );
		}

		/// <summary>
		/// Execute a sql statement using a format string and a set of arguments.
		/// </summary>
		/// <param name="sqlFormat">The sql format string.</param>
		/// <param name="args">The arguments for the string.</param>
		/// <returns>A DataSet object.</returns>
		public DataSet SqlDataSet( string sqlFormat, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sqlFormat, "sqlFormat" );
			// args can be null or array of objects

			return SqlDataSet( string.Format( sqlFormat, args ) );
		}

		/// <summary>
		/// Execute a reader sql statement.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>A DataSet object.</returns>
		public DataSet SqlDataSet( string sql )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sql, "sql" );

			return (DataSet) ExecuteCommand( SqlExecuteType.DataSet, BuildSqlCommand( sql ) );
		}

		/// <summary>
		/// Execute a data set stored procedure.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>A DataSet object.</returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public DataSet StoredProcDataSet( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return (DataSet) ExecuteStoredProc( SqlExecuteType.DataSet, proc, args );
		}

		/// <summary>
		/// Execute a sql statement using a format string and a set of arguments.
		/// Returns the first column of the first row in the result set returned by the 
		/// query. Extra columns or rows are ignored.
		/// </summary>
		/// <param name="sqlFormat">The sql format string.</param>
		/// <param name="args">The arguments for the string.</param>
		/// <returns>
		/// The first column of the first row in the result set, or a null 
		/// reference if the result set is empty.
		/// </returns>
		public object SqlScalar( string sqlFormat, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sqlFormat, "sqlFormat" );
			// args can be null or array of objects

			return SqlScalar( string.Format( sqlFormat, args ) );
		}

		/// <summary>
		/// Execute the sql statement and returns the first column of the first row in 
		/// the result set returned by the query. Extra columns or rows are ignored.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>
		/// The first column of the first row in the result set, or a null 
		/// reference if the result set is empty.
		/// </returns>
		public object SqlScalar( string sql )
		{
			// Check Params
			ParamCode.AssertNotEmpty( sql, "sql" );

			return ExecuteCommand( SqlExecuteType.Scalar, BuildSqlCommand( sql ) );
		}

		/// <summary>
		/// Execute a stored procedure and returns the first column of the first row in 
		/// the result set returned by the query. Extra columns or rows are ignored.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>
		/// The first column of the first row in the result set, or a null 
		/// reference if the result set is empty.
		/// </returns>
		/// <remarks>
		/// When calling this method the argument list passed in will be directly
		/// mapped to the stored procedure arguments.  First argument to first 
		/// arugment, second to second, etc...
		/// </remarks>
		public object StoredProcScalar( string proc, params object[] args )
		{
			// Check Params
			ParamCode.AssertNotEmpty( proc, "proc" );

			return ExecuteStoredProc( SqlExecuteType.Scalar, proc, args );
		}

		/// <summary>
		/// Build the command object with the given sql statement.
		/// </summary>
		/// <param name="sql">Sql string to execute</param>
		/// <returns>The sql command object.</returns>
		private SqlCommand BuildSqlCommand( string sql )
		{
			SqlCommand cmd = m_sqlConnection.CreateCommand();
			cmd.CommandType = CommandType.Text;
			cmd.CommandTimeout = m_queryTimeout;
			cmd.CommandText = sql;
			cmd.Transaction = m_sqlTransaction;

			return cmd;
		}

		/// <summary>
		/// Build the command object with the values and parameter for a stored procedure.
		/// </summary>
		/// <param name="proc">Name of the stored procedure</param>
		/// <param name="args">List of arguments to the stored procedure</param>
		/// <returns>The sql command object.</returns>
		private SqlCommand BuildStoredProcCommand( string proc, params object[] args )
		{
			// Setup the sql command object
			SqlCommand cmd = m_sqlConnection.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandTimeout = m_queryTimeout;
			cmd.CommandText = proc;
			cmd.Transaction = m_sqlTransaction;

			// Add the parameters
			if( args != null )
			{
				ArrayList paramList = m_databaseConnection.GetStoredProcParameterList( proc );
				
				int count = args.Length;
				int x = 0;

				foreach( SqlParameter param in paramList )
				{
					if( param.Direction == ParameterDirection.Input ||
						param.Direction == ParameterDirection.InputOutput )
					{
						if( x < count )
						{
							cmd.Parameters.AddWithValue( param.ParameterName, args[ x ] );
							x++;
						}
					}
					else if( param.Direction == ParameterDirection.ReturnValue )
					{
						SqlParameter retParam = new SqlParameter();
						retParam.ParameterName = param.ParameterName;
						retParam.Direction = param.Direction;

						cmd.Parameters.Add( retParam );
					}
				}
			}

			return cmd;
		}

		/// <summary>
		/// Execute the sql command object.
		/// </summary>
		/// <param name="type">The type of executeion to do.</param>
		/// <param name="cmd">The command object</param>
		/// <returns>Result object for the given type of executeion.</returns>
		private object ExecuteCommand( SqlExecuteType type, SqlCommand cmd )
		{
			int tries = -1;

			while( true )
			{
				try
				{
					switch( type )
					{
						case SqlExecuteType.NonQuery:
							return cmd.ExecuteNonQuery();

						case SqlExecuteType.NonQueryWithReturn:
							cmd.ExecuteNonQuery();
							return cmd.Parameters[ RETURN_PARAMETER ].Value;

						case SqlExecuteType.Reader:
							return cmd.ExecuteReader();

						case SqlExecuteType.DataTable:
							using( IDataReader reader = cmd.ExecuteReader() )
							{
								DataTable dataTable = new DataTable();
								dataTable.Load( reader );
								return dataTable;
							}

						case SqlExecuteType.DataSet:
							DataSet dataSet = new DataSet();
							new SqlDataAdapter( cmd ).Fill( dataSet );
							return dataSet;

						case SqlExecuteType.Scalar:
							return cmd.ExecuteScalar();

						default:
							throw new Exception( "Unknown SqlExecuteType" );
					}
				}
				catch( SqlException e )
				{
					// If this is a timeout error the retry
					// TODO: what is the timeout number?
					if( e.Number == 0 && tries < m_timeoutRetries )
					{
						tries++;
					}
					else
					{
						throw e;
					}
				}
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="type"></param>
		/// <param name="proc"></param>
		/// <param name="args"></param>
		/// <returns></returns>
		private object ExecuteStoredProc( SqlExecuteType type, string proc, params object[] args )
		{
			SqlCommand cmd = BuildStoredProcCommand( proc, args );
			return ExecuteCommand( type, cmd );
		}
	}
}
